import string
import random
import time

import pymysql
from flask import Flask, json, request, jsonify
import requests

app = Flask(__name__)

appId = "wx755f19a5bf163106"
appSecret = "0b8107bc8941a9b0d320c776ebb5a3db"


@app.route("/")
def hello_world():
    return "<p>Hello, World!</p>"


# 用户登录获取openid————login
@app.route("/login", methods=['GET', 'POST'])
def login():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))

    js_code = json_data.get("code")
    nickname = json_data.get("nickName")
    num = json_data.get("sex")
    if num == 0:
        sex = '男'
    else:
        sex = '女'

    url = "https://api.weixin.qq.com/sns/jscode2session?appid=" + appId + "&secret=" \
          + appSecret + "&js_code=" + js_code + "&grant_type=authorization_code"
    response = requests.get(url)
    res = response.json()
    if res:
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="zhang1yun2kang3",
                             database="MyAppDB",
                             charset="utf8")
        print("连接上了！！！！")
        cursor = db.cursor()

        sql = "INSERT IGNORE INTO `users`(openid, sex, nickName) VALUES (%s, %s, %s) "
        # noinspection PyBroadException
        try:
            cursor.execute(sql, (res['openid'], sex, nickname))
            db.commit()
            cursor.close()
            db.close()
            return jsonify({"status": 200, "openid": res['openid']})
        except Exception as e:
            print("插入异常！！")
            print(e)
            db.rollback()
            cursor.close()
            db.close()
            return jsonify({"status": 500})
    else:
        return jsonify({"status": 500})


# 用户获取个人信息————getUserInfo
@app.route("/getUserInfo", methods=['GET'])
def getUserInfo():
    # data = request.get_data()
    # json_data = json.loads(data.decode("UTF-8"))

    # openid = json_data.get("openid")
    openid = request.args.get("openid")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()

    sql = "SELECT phone, sex FROM `users` WHERE openid = %s"
    try:
        cursor.execute(sql, openid)
        results = cursor.fetchall()
        for row in results:
            phone = row[0]
            sex = row[1]
            if not phone:
                phone = "未设置"
            if not sex:
                sex = "未设置"
            cursor.close()
            db.close()
            return jsonify({"status": 200, "sex": sex, "phone": phone})
    except Exception as e:
        print("查询出错: " + e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "sex": "未设置", "phone": "未设置"})


# 用户修改性别————updUserSex
@app.route("/updUserSex", methods=['GET', 'POST'])
def updUserSex():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))

    sex = json_data.get("sex")
    openid = json_data.get("openid")
    if sex != '男' and sex != "女":
        return jsonify({"status": 500, "msg": "请填写男或女！"})
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    cursor = db.cursor()
    sql = 'UPDATE users SET sex = %s  WHERE openid = %s'
    try:
        cursor.execute(sql, (sex, openid))
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200, "sex": sex})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "修改失败，请稍后重试"})


# 用户修改手机号————updUserPhone
@app.route("/updUserPhone", methods=['POST'])
def updUserPhone():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))

    phone = json_data.get("phone")
    openid = json_data.get("openid")
    print(phone)
    if len(phone) != 11:
        return jsonify({"status": 500, "msg": "请填写正确的11位手机号码"})
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    cursor = db.cursor()
    sql = 'UPDATE users SET phone = %s  WHERE openid = %s'
    try:
        cursor.execute(sql, (phone, openid))
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200, "phone": phone})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "修改失败，请稍后重试"})


# 加入班级————joinClass
@app.route("/joinClass", methods=['POST'])
def joinClass():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))

    openid = json_data.get("openid")
    class_code = json_data.get("code")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    sql1 = "SELECT id FROM classes WHERE code = %s"
    sql2 = "SELECT id, nickName FROM users WHERE openid = %s"
    try:
        cursor.execute(sql1, class_code)
        results = cursor.fetchall()
        for row in results:
            cid = row[0]
        cursor.close()

        cursor = db.cursor()
        cursor.execute(sql2, openid)
        results = cursor.fetchall()
        for row in results:
            uid = row[0]
            nickname = row[1]
        cursor.close()
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        return jsonify({"status": 500, "msg": "班级不存在"})

    sql = "INSERT INTO classes_users(cid, uid, nickName) VALUES ('%d', '%d', '%s')" % (cid, uid, nickname)
    sql3 = "SELECT name FROM classes WHERE id = %d" % cid

    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        cursor.close()

        cursor = db.cursor()
        cursor.execute(sql3)
        names = cursor.fetchall()
        for row in names:
            name = row[0]
        cursor.close()

        sql_num = "UPDATE classes SET num = num+1 WHERE id = %d" % cid
        cursor = db.cursor()
        cursor.execute(sql_num)
        db.commit()
        cursor.close()

        return jsonify({"status": 200, "name": name})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "你已是班级成员，请勿重复添加"})


# 创建班级————addClass
@app.route("/addClass", methods=['POST', 'GET'])
def addClass():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))

    openid = json_data.get("openid")
    name = json_data.get("name")

    sql = "SELECT id FROM users WHERE openid = %s"
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    cursor.execute(sql, openid)
    results = cursor.fetchall()
    for row in results:
        uid = row[0]

    class_code = ''.join(random.sample(string.ascii_letters + string.digits, 8))
    sql_insert = "INSERT INTO classes(uid, name, num, code) VALUES ('%d','%s','%d','%s')" % (uid, name, 0, class_code)
    try:
        cursor.execute(sql_insert)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200, "code": class_code})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "创建失败，请重试"})


# 查询创建的签到班级————getUserClass
@app.route("/getUserClass", methods=['GET'])
def getUserClass():
    openid = request.args.get("openid")

    sql_id = "SELECT id FROM users WHERE openid = %s"
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    cursor.execute(sql_id, openid)

    tea_id = cursor.fetchone()
    print(tea_id)

    data = []

    try:
        sql_class = "SELECT id, name, num, code FROM classes WHERE uid = %d" % tea_id
        cursor.execute(sql_class)
        results = cursor.fetchall()
        if not results:
            cursor.close()
            db.close()
            return jsonify({"status": 500})

        for row in results:
            i_data = {}
            users = []
            cla = {"id": row[0], "name": row[1], "num": row[2], "code": row[3]}

            sql_stu = "SELECT uid, nickName FROM classes_users WHERE cid = %d" % cla.get("id")
            db.ping(reconnect=True)
            cursor.execute(sql_stu)
            uid_group = cursor.fetchall()
            for i in uid_group:
                user = {"id": i[0], "nickName": i[1]}

                sql_user = "SELECT phone, sex FROM users WHERE id = %d" % user.get("id")
                db1 = pymysql.connect(host="localhost",
                                      user="root",
                                      password="zhang1yun2kang3",
                                      database="MyAppDB",
                                      charset="utf8")
                print("再一次连接上了！！！！")
                cur = db1.cursor()
                cur.execute(sql_user)
                info = cur.fetchall()
                for j in info:
                    user["phone"] = j[0]
                    user["sex"] = j[1]

                users.append(user.copy())
                cur.close()
                db1.close()

            i_data["class"] = cla
            i_data["users"] = users
            i_data["hidden"] = False
            i_data["delHidden"] = False
            data.append(i_data.copy())

        cursor.close()
        db.close()
        return jsonify({"status": 200, "data": data})

    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500})


# 用户修改自己的班级昵称————updUserClassNickName
@app.route("/updUserClassNickName", methods=['PUT'])
def updUserClassNickName():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    # print(openid)
    cid = json_data.get("cid")
    nickName = json_data.get("nickName")

    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = "UPDATE classes_users SET nickName = '%s'  WHERE cid = '%d'" % (nickName, cid)  # 待更新

    try:
        cursor.execute(sql)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "修改失败，请稍后重试"})


# 用户退出已加入的班级————exitClass
@app.route("/exitClass", methods=['POST'])
def exitClass():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    cid = json_data.get("cid")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = "DELETE FROM classes_users WHERE cid = '%d' " % cid
    sql1 = "UPDATE classes SET num = num-1 WHERE(id= '%d' and uid = " \
           "(SELECT id FROM users WHERE openid = '%s'))" % (cid, openid)
    sql2 = "DELETE FROM sign WHERE csid IN (SELECT id FROM createSign WHERE cid = '%d') AND " \
           "uid = (SELECT id FROM users WHERE openid = '%s')" % (cid, openid)
    try:
        cursor.execute(sql)
        cursor.execute(sql1)
        cursor.execute(sql2)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "退出失败，请重试"})


# 用户删除已创建的班级————delClass
@app.route("/delClass", methods=['POST'])
def delClass():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    cid = json_data.get("cid")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = "DELETE FROM classes WHERE id = %d" % cid
    sql1 = "DELETE FROM classes_users WHERE cid = %d" % cid
    sql2 = "DELETE FROM sign WHERE csid IN (SELECT id FROM createSign WHERE cid = %d)" % cid
    sql3 = "DELETE FROM createSign WHERE cid = %d" % cid
    try:
        cursor.execute(sql)
        cursor.execute(sql1)
        cursor.execute(sql2)
        cursor.execute(sql3)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "解散失败，请重试"})


# 用户删除班级成员————teaDelClassUser
@app.route("/teaDelClassUser", methods=['POST'])
def teaDelClassUser():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    cid = json_data.get("cid")
    uid = json_data.get("uid")
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    # print("database yes")
    cursor = db.cursor()
    sql = "DELETE FROM classes_users WHERE cid = %d and uid = %d" % (cid, uid)
    sql1 = "UPDATE classes SET num=num-1 WHERE uid = %d" % uid
    sql2 = "DELETE FROM sign WHERE csid IN (SELECT id FROM createSign WHERE cid = '%d') AND " \
           "uid = (SELECT id FROM users WHERE openid = '%s')" % (cid, openid)

    try:
        cursor.execute(sql)
        cursor.execute(sql1)
        cursor.execute(sql2)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "移除失败，请重试"})


# 查询加入的班级————getUserJoinClass
@app.route("/getUserJoinClass", methods=['GET'])
def getUserJoinClass():
    openid = request.args.get("openid")

    sql_user = "SELECT id FROM users WHERE openid = %s"
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    user = {}

    data = []

    try:
        cursor.execute(sql_user, openid)
        result = cursor.fetchall()
        for row in result:
            user["id"] = row[0]

        sql_cla = "SELECT cid, nickName FROM classes_users WHERE uid = %d" % user.get("id")
        db.ping(reconnect=True)
        cursor.execute(sql_cla)
        results = cursor.fetchall()
        if not results:
            cursor.close()
            db.close()
            return jsonify({"status": 500})
        else:
            for row in results:
                i_data = {}
                cla = {"id": row[0]}
                user = {"nickName": row[1]}
                sql_info = "SELECT name, num, code FROM classes WHERE id = %d" % cla.get("id")
                db1 = pymysql.connect(host="localhost",
                                      user="root",
                                      password="zhang1yun2kang3",
                                      database="MyAppDB",
                                      charset="utf8")
                print("再一次连接上了！！！！")
                cur = db1.cursor()
                cur.execute(sql_info)
                info = cur.fetchall()

                for i in info:
                    cla["name"] = i[0]
                    cla["num"] = i[1]
                    cla["code"] = i[2]

                cur.close()
                db1.close()
                i_data["class"] = cla
                i_data["hidden"] = False
                i_data["delHidden"] = False
                i_data["showUpd"] = False
                i_data["tempNickName"] = ""
                i_data["user"] = user

                data.append(i_data.copy())
            cursor.close()
            db.close()
            return jsonify({"status": 200, "data": data})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500})


# 发起签到-加载班级列表————selUserCreateClass
@app.route("/selUserCreateClass", methods=['GET'])
def selUserCreateClass():
    openid = request.args.get("openid")

    sql_uid = "SELECT id FROM users WHERE openid = %s"
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    cursor.execute(sql_uid, openid)
    uid = cursor.fetchone()
    i_data = {}
    data = []

    sql_cla = "SELECT id, name, num FROM classes WHERE uid = %d" % uid
    try:
        cursor.execute(sql_cla)
        results = cursor.fetchall()
        for row in results:
            i_data["id"] = row[0]
            i_data["name"] = row[1]
            i_data["num"] = row[2]
            data.append(i_data.copy())
        cursor.close()
        db.close()
        return jsonify({"status": 200, "data": data})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500})


# 发起签到-普通签到————createSign
@app.route("/createSign", methods=['POST'])
def createSign():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    cid = int(json_data.get("cid"))
    valid_time = json_data.get("validTime")
    print(type(cid))
    print(cid)
    print(type(time))
    sql_ins = "INSERT INTO createSign(cid, validTime) VALUES ('%d', '%d')" % (cid, valid_time)
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    try:
        db.ping(reconnect=True)
        cursor.execute(sql_ins)
        csid = cursor.lastrowid
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "mag": "发起失败，请稍后重试"})

    sql_uid = "SELECT uid FROM classes_users WHERE cid = %d" % cid
    db.ping(reconnect=True)
    cursor.execute(sql_uid)
    results = cursor.fetchall()
    for row in results:
        uid = row[0]
        sql_sign = "INSERT INTO sign(csid, uid, finish) VALUES ('%d', '%d', '%s')" % (csid, uid, '0')
        db1 = pymysql.connect(host="localhost",
                              user="root",
                              password="zhang1yun2kang3",
                              database="MyAppDB",
                              charset="utf8")
        print("再一次连接上了！！！！")
        cur = db1.cursor()
        cur.execute(sql_sign)
        db1.commit()
        cur.close()
        db1.close()
    cursor.close()
    db.close()
    return jsonify({"status": 200})


# 用户签到-普通签到————sign        # 待测试
@app.route("/sign", methods=['POST'])
def sign():
    data = request.get_data()
    json_data = json.loads(data.decode("UTF-8"))
    openid = json_data.get("openid")
    signid = int(json_data.get("signid"))

    sql_sign = "SELECT createTime, validTime FROM createSign WHERE id = %d" % signid
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    try:
        cursor.execute(sql_sign)
        results = cursor.fetchall()
        for row in results:
            create_time = str(row[0])
            valid_time = row[1]
        # print(type(create_time))

        now = time.time()
        print(create_time)
        start = time.mktime(time.strptime(create_time, "%Y-%m-%d %H:%M:%S"))
        print(start)
        print(now)
        if valid_time == 0 or (start + valid_time) >= now:
            finish = '1'
        else:
            finish = '2'
        sql_put = "UPDATE sign SET finish = '%s' WHERE (csid = '%d' AND " \
                  "uid = (SELECT id FROM users WHERE openid = '%s'))" % (finish, signid, openid)
        cursor.execute(sql_put)
        db.commit()
        cursor.close()
        db.close()
        return jsonify({"status": 200, "signTime": time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(int(now)))})
    except Exception as e:
        print(e)
        return jsonify({"status": 500, "msg": "签到失败，请重试"})


# 随机点名————getOneClassUserInfo
@app.route("/getOneClassUserInfo", methods=['GET'])
def getOneClassUserInfo():
    cid = int(request.args.get("cid"))
    data = []
    sql_stus = "SELECT uid, nickName FROM classes_users WHERE cid = %d" % cid
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    try:
        cursor.execute(sql_stus)
        stus = cursor.fetchall()
        for row in stus:
            stu = {"nickName": row[1]}
            sql_info = "SELECT sex, phone FROM users WHERE id = %d" % row[0]
            db1 = pymysql.connect(host="localhost",
                                  user="root",
                                  password="zhang1yun2kang3",
                                  database="MyAppDB",
                                  charset="utf8")
            print("连接上了！！！！")
            cur = db1.cursor()
            cur.execute(sql_info)
            info = cur.fetchall()
            for i in info:
                stu["sex"] = i[0]
                stu["phone"] = i[1]
            cur.close()
            db1.close()
            # cursor.close()
            # db.close()
            data.append(stu.copy())
        return jsonify({"status": 200, "data": data})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "点名失败，稍后重试"})


# 查询发起的签到————getSignResult
@app.route("/getSignResult", methods=['GET', 'POST'])
def getSignResult():
    openid = request.args.get("openid")
    data = {}
    sign = []
    psign = []
    gsign = []
    lsign = []

    # 查询 sign数据部分
    sql_class = "SELECT id, name FROM classes WHERE uid = (SELECT id FROM users WHERE openid = %s)"
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyAppDB",
                         charset="utf8")
    print("连接上了！！！！")
    cursor = db.cursor()
    try:
        db.ping(reconnect=True)
        cursor.execute(sql_class, openid)
        classes = cursor.fetchall()
        for row in classes:
            i_sign = {"name": row[1]}
            sql_signs = "SELECT id, createTime FROM createSign WHERE cid = %d" % row[0]
            db1 = pymysql.connect(host="localhost",
                                  user="root",
                                  password="zhang1yun2kang3",
                                  database="MyAppDB",
                                  charset="utf8")
            print("连接上了！！！！")
            cur = db1.cursor()
            db1.ping(reconnect=True)
            cur.execute(sql_signs)
            results = cur.fetchall()
            if not results:
                flag1 = 0
            else:
                for i in results:
                    users = []
                    i_sign["createTime"] = str(i[1])
                    i_sign["hidden"] = False
                    sql_user = "SELECT uid, signTime, finish FROM sign WHERE csid = %d" % i[0]
                    db2 = pymysql.connect(host="localhost",
                                          user="root",
                                          password="zhang1yun2kang3",
                                          database="MyAppDB",
                                          charset="utf8")
                    print("连接上了！！！！")
                    cur1 = db2.cursor()
                    db2.ping(reconnect=True)
                    cur1.execute(sql_user)
                    info = cur1.fetchall()
                    for j in info:
                        user = {"signTime": str(j[1])}
                        if j[2] == '0':
                            user["result"] = "未签到"
                        else:
                            if j[2] == '1':
                                user["result"] = "已签到"
                            else:
                                user["result"] = "超时"
                        sql_name = "SELECT nickName FROM classes_users WHERE cid = %d AND uid = %d" % (row[0], j[0])
                        db3 = pymysql.connect(host="localhost",
                                              user="root",
                                              password="zhang1yun2kang3",
                                              database="MyAppDB",
                                              charset="utf8")
                        print("连接上了！！！！")
                        cur2 = db3.cursor()
                        db3.ping(reconnect=True)
                        cur2.execute(sql_name)
                        user["nickName"] = cur2.fetchone()
                        cur2.close()
                        db3.close()
                        users.append(user.copy())
                    i_sign["users"] = users
                    cur1.close()
                    db2.close()
                    sign.append(i_sign.copy())
        data["sign"] = sign

        # 查询 psign数据部分
        sql_class = "SELECT id, name FROM classes WHERE uid = (SELECT id FROM users WHERE openid = %s)"
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="zhang1yun2kang3",
                             database="MyAppDB",
                             charset="utf8")
        print("连接上了！！！！")
        cursor = db.cursor()
        db.ping(reconnect=True)
        cursor.execute(sql_class, openid)
        classes = cursor.fetchall()
        for row in classes:
            i_psign = {"name": row[1]}
            sql_signs = "SELECT id, createTime FROM createPSign WHERE cid = %d" % row[0]
            db1 = pymysql.connect(host="localhost",
                                  user="root",
                                  password="zhang1yun2kang3",
                                  database="MyAppDB",
                                  charset="utf8")
            print("连接上了！！！！")
            cur = db1.cursor()
            db1.ping(reconnect=True)
            cur.execute(sql_signs)
            results = cur.fetchall()
            if not results:
                flag2 = 0
            else:
                for i in results:
                    users = []
                    i_psign["createTime"] = str(i[1])
                    i_psign["hidden"] = False

                    sql_user = "SELECT uid, signTime, finish FROM psign WHERE cpsid = %d" % i[0]
                    db2 = pymysql.connect(host="localhost",
                                          user="root",
                                          password="zhang1yun2kang3",
                                          database="MyAppDB",
                                          charset="utf8")
                    print("连接上了！！！！")
                    cur1 = db2.cursor()
                    db2.ping(reconnect=True)
                    cur1.execute(sql_user)
                    info = cur1.fetchall()
                    for j in info:
                        user = {"signTime": str(j[1])}
                        if j[2] == '0':
                            user["result"] = "未签到"
                        else:
                            if j[2] == '1':
                                user["result"] = "已签到"
                            else:
                                user["result"] = "超时"
                        sql_name = "SELECT nickName FROM classes_users WHERE cid = %d AND uid = %d" % (row[0], j[0])
                        db3 = pymysql.connect(host="localhost",
                                              user="root",
                                              password="zhang1yun2kang3",
                                              database="MyAppDB",
                                              charset="utf8")
                        print("连接上了！！！！")
                        cur2 = db3.cursor()
                        db3.ping(reconnect=True)
                        cur2.execute(sql_name)
                        user["nickName"] = cur2.fetchone()
                        cur2.close()
                        db3.close()
                        users.append(user.copy())
                    i_psign["users"] = users
                    cur1.close()
                    db2.close()
                    psign.append(i_psign.copy())
        data["psign"] = psign

        # 查询 gsign数据部分
        sql_class = "SELECT id, name FROM classes WHERE uid = (SELECT id FROM users WHERE openid = %s)"
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="zhang1yun2kang3",
                             database="MyAppDB",
                             charset="utf8")
        print("连接上了！！！！")
        cursor = db.cursor()
        db.ping(reconnect=True)
        cursor.execute(sql_class, openid)
        classes = cursor.fetchall()
        for row in classes:
            i_gsign = {"name": row[1]}
            sql_signs = "SELECT id, createTime FROM createGSign WHERE cid = %d" % row[0]
            db1 = pymysql.connect(host="localhost",
                                  user="root",
                                  password="zhang1yun2kang3",
                                  database="MyAppDB",
                                  charset="utf8")
            print("连接上了！！！！")
            cur = db1.cursor()
            db1.ping(reconnect=True)
            cur.execute(sql_signs)
            results = cur.fetchall()
            if not results:
                flag3 = 0
            else:
                for i in results:
                    users = []
                    i_gsign["createTime"] = str(i[1])
                    i_gsign["hidden"] = False

                    sql_user = "SELECT uid, signTime, finish FROM gsign WHERE cgsid = %d" % i[0]
                    db2 = pymysql.connect(host="localhost",
                                          user="root",
                                          password="zhang1yun2kang3",
                                          database="MyAppDB",
                                          charset="utf8")
                    print("连接上了！！！！")
                    cur1 = db2.cursor()
                    db2.ping(reconnect=True)
                    cur1.execute(sql_user)
                    info = cur1.fetchall()
                    for j in info:
                        user = {"signTime": str(j[1])}
                        if j[2] == '0':
                            user["result"] = "未签到"
                        else:
                            if j[2] == '1':
                                user["result"] = "已签到"
                            else:
                                user["result"] = "超时"
                        sql_name = "SELECT nickName FROM classes_users WHERE cid = %d AND uid = %d" % (row[0], j[0])
                        db3 = pymysql.connect(host="localhost",
                                              user="root",
                                              password="zhang1yun2kang3",
                                              database="MyAppDB",
                                              charset="utf8")
                        print("连接上了！！！！")
                        cur2 = db3.cursor()
                        db3.ping(reconnect=True)
                        cur2.execute(sql_name)
                        user["nickName"] = cur2.fetchone()
                        cur2.close()
                        db3.close()
                        users.append(user.copy())
                    i_gsign["users"] = users
                    cur1.close()
                    db2.close()
                    gsign.append(i_gsign.copy())
        data["gsign"] = gsign

        # 查询 lsign数据部分
        sql_class = "SELECT id, name FROM classes WHERE uid = (SELECT id FROM users WHERE openid = %s)"
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="zhang1yun2kang3",
                             database="MyAppDB",
                             charset="utf8")
        print("连接上了！！！！")
        cursor = db.cursor()
        db.ping(reconnect=True)
        cursor.execute(sql_class, openid)
        classes = cursor.fetchall()
        for row in classes:
            i_lsign = {"name": row[1]}
            sql_signs = "SELECT id, createTime FROM createLSign WHERE cid = %d" % row[0]
            db1 = pymysql.connect(host="localhost",
                                  user="root",
                                  password="zhang1yun2kang3",
                                  database="MyAppDB",
                                  charset="utf8")
            print("连接上了！！！！")
            cur = db1.cursor()
            db1.ping(reconnect=True)
            cur.execute(sql_signs)
            results = cur.fetchall()
            if not results:
                flag4 = 0
            else:
                for i in results:
                    users = []
                    i_lsign["createTime"] = str(i[1])
                    i_lsign["hidden"] = False

                    sql_user = "SELECT uid, signTime, finish, location FROM lsign WHERE clsid = %d" % i[0]
                    db2 = pymysql.connect(host="localhost",
                                          user="root",
                                          password="zhang1yun2kang3",
                                          database="MyAppDB",
                                          charset="utf8")
                    print("连接上了！！！！")
                    cur1 = db2.cursor()
                    db2.ping(reconnect=True)
                    cur1.execute(sql_user)
                    info = cur1.fetchall()
                    for j in info:
                        user = {"signTime": str(j[1]), "location": j[3]}
                        if j[2] == '0':
                            user["result"] = "未签到"
                        else:
                            if j[2] == '1':
                                user["result"] = "已签到"
                            else:
                                user["result"] = "超时"
                        sql_name = "SELECT nickName FROM classes_users WHERE cid = %d AND uid = %d" % (row[0], j[0])
                        db3 = pymysql.connect(host="localhost",
                                              user="root",
                                              password="zhang1yun2kang3",
                                              database="MyAppDB",
                                              charset="utf8")
                        print("连接上了！！！！")
                        cur2 = db3.cursor()
                        db3.ping(reconnect=True)
                        cur2.execute(sql_name)
                        user["nickName"] = cur2.fetchone()
                        cur2.close()
                        db3.close()
                        user["showLocation"] = False
                        users.append(user.copy())
                    i_lsign["users"] = users
                    cur1.close()
                    db2.close()
                    lsign.append(i_lsign.copy())
        data["lsign"] = lsign

        return jsonify({"status": 200, "data": {"total": len(sign) + len(psign) + len(gsign) + len(lsign),
                                                "sign": sign, "psign": psign, "gsign": gsign, "lsign": lsign}})
    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "出错了，稍后重试"})


# 查询参与的签到————getJoinedSign
@app.route("/getJoinedSign", methods=['GET', 'POST'])
def getJoinedSign():
    openid = request.args.get("openid")
    data = {}
    sign = []
    psign = []
    gsign = []
    lsign = []

    try:
        # 查询 sign数据
        sql_sign = "SELECT csid, signTime, finish FROM sign WHERE uid = (SELECT id FROM users WHERE openid = %s)"
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="zhang1yun2kang3",
                             database="MyAppDB",
                             charset="utf8")
        print("连接上了！！！！")
        cursor = db.cursor()
        db.ping(reconnect=True)
        cursor.execute(sql_sign, openid)
        results = cursor.fetchall()
        if not results:
            flag1 = 0
        else:
            for row in results:
                signid = row[0]
                user = {"signTime": str(row[1])}
                if row[2] == '0':
                    user["result"] = "未签到"
                else:
                    if row[2] == '1':
                        user["result"] = "已签到"
                    else:
                        user["result"] = "超时"
                i_sign = {"user": user, "hidden": False}

                sql_start = "SELECT cid, createTime FROM createSign WHERE id = %d" % signid
                db1 = pymysql.connect(host="localhost",
                                      user="root",
                                      password="zhang1yun2kang3",
                                      database="MyAppDB",
                                      charset="utf8")
                print("再一次连接上了！！！！")
                cursor1 = db1.cursor()
                db1.ping(reconnect=True)
                cursor1.execute(sql_start)
                result = cursor1.fetchall()
                for i in result:
                    cid = i[0]
                    i_sign["createTime"] = str(i[1])

                sql_name = "SELECT name FROM classes WHERE id = %d" % cid
                db1.ping(reconnect=True)
                cursor1.execute(sql_name)
                name = cursor1.fetchone()
                i_sign["name"] = name
                cursor1.close()
                db1.close()
                sign.append(i_sign.copy())
        data["sign"] = sign
        cursor.close()
        db.close()

        # 查询 psign数据
        sql_sign = "SELECT cpsid, signTime, finish FROM psign WHERE uid = (SELECT id FROM users WHERE openid = %s)"
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="zhang1yun2kang3",
                             database="MyAppDB",
                             charset="utf8")
        print("连接上了！！！！")
        cursor = db.cursor()
        db.ping(reconnect=True)
        cursor.execute(sql_sign, openid)
        results = cursor.fetchall()
        if not results:
            flag2 = 0
        else:
            for row in results:
                signid = row[0]
                user = {"signTime": str(row[1])}
                if row[2] == '0':
                    user["result"] = "未签到"
                else:
                    if row[2] == '1':
                        user["result"] = "已签到"
                    else:
                        user["result"] = "超时"
                i_psign = {"user": user, "hidden": False}

                sql_start = "SELECT cid, createTime FROM createPSign WHERE id = %d" % signid
                db1 = pymysql.connect(host="localhost",
                                      user="root",
                                      password="zhang1yun2kang3",
                                      database="MyAppDB",
                                      charset="utf8")
                print("再一次连接上了！！！！")
                cursor1 = db1.cursor()
                db1.ping(reconnect=True)
                cursor1.execute(sql_start)
                result = cursor1.fetchall()
                for i in result:
                    cid = i[0]
                    i_psign["createTime"] = str(i[1])

                sql_name = "SELECT name FROM classes WHERE id = %d" % cid
                db1.ping(reconnect=True)
                cursor1.execute(sql_name)
                name = cursor1.fetchone()
                i_psign["name"] = name
                cursor1.close()
                db1.close()
                psign.append(i_psign.copy())
        data["psign"] = psign
        cursor.close()
        db.close()
        # 查询 gsign数据
        sql_sign = "SELECT cgsid, signTime, finish FROM gsign WHERE uid = (SELECT id FROM users WHERE openid = %s)"
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="zhang1yun2kang3",
                             database="MyAppDB",
                             charset="utf8")
        print("连接上了！！！！")
        cursor = db.cursor()
        db.ping(reconnect=True)
        cursor.execute(sql_sign, openid)
        results = cursor.fetchall()
        if not results:
            flag3 = 0
        else:
            for row in results:
                signid = row[0]
                user = {"signTime": str(row[1])}
                if row[2] == '0':
                    user["result"] = "未签到"
                else:
                    if row[2] == '1':
                        user["result"] = "已签到"
                    else:
                        user["result"] = "超时"
                i_gsign = {"user": user, "hidden": False}

                sql_start = "SELECT cid, createTime FROM createGSign WHERE id = %d" % signid
                db1 = pymysql.connect(host="localhost",
                                      user="root",
                                      password="zhang1yun2kang3",
                                      database="MyAppDB",
                                      charset="utf8")
                print("再一次连接上了！！！！")
                cursor1 = db1.cursor()
                db1.ping(reconnect=True)
                cursor1.execute(sql_start)
                result = cursor1.fetchall()
                for i in result:
                    cid = i[0]
                    i_gsign["createTime"] = str(i[1])

                sql_name = "SELECT name FROM classes WHERE id = %d" % cid
                db1.ping(reconnect=True)
                cursor1.execute(sql_name)
                name = cursor1.fetchone()
                i_gsign["name"] = name
                cursor1.close()
                db1.close()
                gsign.append(i_gsign.copy())
        data["gsign"] = gsign
        cursor.close()
        db.close()

        # 查询 lsign数据
        sql_sign = "SELECT clsid, signTime, finish, location FROM lsign WHERE uid = " \
                   "(SELECT id FROM users WHERE openid = %s)"
        db = pymysql.connect(host="localhost",
                             user="root",
                             password="zhang1yun2kang3",
                             database="MyAppDB",
                             charset="utf8")
        print("连接上了！！！！")
        cursor = db.cursor()
        db.ping(reconnect=True)
        cursor.execute(sql_sign, openid)
        results = cursor.fetchall()
        if not results:
            flag4 = 0
        else:
            for row in results:
                signid = row[0]
                user = {"signTime": str(row[1]), "location": row[3], "showLocation": False}
                if row[2] == '0':
                    user["result"] = "未签到"
                else:
                    if row[2] == '1':
                        user["result"] = "已签到"
                    else:
                        user["result"] = "超时"
                i_lsign = {"user": user, "hidden": False}

                sql_start = "SELECT cid, createTime FROM createLSign WHERE id = %d" % signid
                db1 = pymysql.connect(host="localhost",
                                      user="root",
                                      password="zhang1yun2kang3",
                                      database="MyAppDB",
                                      charset="utf8")
                print("再一次连接上了！！！！")
                cursor1 = db1.cursor()
                db1.ping(reconnect=True)
                cursor1.execute(sql_start)
                result = cursor1.fetchall()
                for i in result:
                    cid = i[0]
                    i_lsign["createTime"] = str(i[1])

                sql_name = "SELECT name FROM classes WHERE id = %d" % cid
                db1.ping(reconnect=True)
                cursor1.execute(sql_name)
                name = cursor1.fetchone()
                i_lsign["name"] = name
                cursor1.close()
                db1.close()
                lsign.append(i_lsign.copy())
        data["lsign"] = lsign
        cursor.close()
        db.close()

        return jsonify({"status": 200, "data": {"total": len(sign) + len(psign) + len(gsign) + len(lsign),
                                                "sign": sign, "psign": psign, "gsign": gsign, "lsign": lsign}})

    except Exception as e:
        print(e)
        db.rollback()
        return jsonify({"status": 500, "msg": "暂无数据，稍后重试"})


# 查看消息提醒————getSignMsg
@app.route("/getSignMsg", methods=['GET'])
def getSignMsg():
    openid = request.args.get("openid")
    data = {}
    sign = []
    psign = []
    gsign = []
    lsign = []
    # i_sign = {}
    db = pymysql.connect(host="localhost",
                         user="root",
                         password="zhang1yun2kang3",
                         database="MyappDB",
                         charset="utf8")
    print("database yes")
    cursor = db.cursor()

    # sql = "SELECT name,id from classes WHERE id = (SELECT cid FROM createSign WHERE id=(SELECT csid FROM sign WHERE
    # " \ "uid=(SELECT id FROM users WHERE openid = '%s')))" % openid
    sql1 = "SELECT csid FROM sign WHERE (uid=(SELECT id FROM users WHERE openid = '%s') AND finish = '0')" % openid
    # sql2 = "SELECT * FROM info inner join info1"
    try:
        cursor.execute(sql1)
        info = cursor.fetchall()
        for i in info:
            i_sign = {"signid": i[0]}
            # print(i[0])
            sql2 = "SELECT name FROM classes WHERE id =  (SELECT cid FROM createSign WHERE id=%d)" % i[0]
            # print(i[0])
            # db1 = pymysql.connect(host="localhost",
            #                       user="root",
            #                       password="123456",
            #                       database="ayh",
            #                       charset="utf8")
            # print("database yes2")
            cursor.execute(sql2)
            # cur = db.cursor()
            # cur.execute(sql2)
            info1 = cursor.fetchall()
            for j in info1:
                # print(j[0])
                i_sign["name"] = j[0]
            sign.append(i_sign.copy())
            # print(sign)
            # cursor.close()
            # db1.close()
        cursor.close()
        db.close()
        data = {
            "sign": sign,
            "psign": psign,
            "gsign": gsign,
            "lsign": lsign
        }
        # print(data)
        return jsonify({"status": 200, "data": data})

    except Exception as e:
        print(e)
        db.rollback()
        cursor.close()
        db.close()
        return jsonify({"status": 500, "msg": "稍后重试"})


# 发起签到-密码签到————createPSign
@app.route("/createPSign", methods=['POST'])
def createPSign():
    return jsonify({"status": 500, "msg": "暂未开放，敬请期待"})


# 发起签到-手势签到————createGSign
@app.route("/createGSign", methods=['POST'])
def createGSign():
    return jsonify({"status": 500, "msg": "暂未开放，敬请期待"})


# 发起签到-位置签到————createLSign
@app.route("/createLSign", methods=['POST'])
def createLSign():
    return jsonify({"status": 500, "msg": "暂未开放，敬请期待"})

# 随机点名————getOneClassUserInfo
# @app.route("/getOneClassUserInfo", methods=['GET'])
# def getOneClassUserInfo():
#     return jsonify({"status": 500, "msg": "暂未开放，敬请期待"})
